import pandas as pd
import numpy as np


def createDataframe():
    """
    创建DataFrame各种方法
    :return:
    """
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    # columns：列标签
    columns = ['身高(cm)', '体重(kg)']
    # 表示行标签
    index = [101, 102, 103]
    # data：表示数据，可以是ndarray数组、series对象、列表、字典等。
    data = [
        [160, 60],
        [160, 70],
        [161, 61],
    ]

    # 创建DataFrame 指定数据， columns自动生成
    df_1 = pd.DataFrame(data=data)
    print(df_1)

    # 创建DataFrame 指定数据， 指定columns
    df_2 = pd.DataFrame(data=data, columns=columns)
    print(df_2)

    # 创建DataFrame 指定数据， 指定columns，指定索引
    df_3 = pd.DataFrame(data=data, columns=columns, index=index)
    print(df_3)

    tuple_data = {
        '性别': '男',
        '身高(cm)': [160, 160, 161],
        '体重(kg)': [60, 70, 61]
    }

    # 创建DataFrame 通过tuple指定data和columns
    df_4 = pd.DataFrame(tuple_data, index=index)
    print(df_4)


def forFun():
    """
    for循环遍历
    :return:
    """
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    # columns：列标签（索引）。
    columns = ['身高(cm)', '体重(kg)']
    # 表示行标签（索引）。
    index = [101, 102, 103, 104, 105, 106]
    # data：表示数据，可以是ndarray数组、series对象、列表、字典等。
    data = [
        [160, 60],
        [160, 70],
        [161, 61],
        [170, 75],
        [171, 60],
        [170, 80]
    ]

    df = pd.DataFrame(data=data, columns=columns, index=index)

    print("================== 遍历列")
    for col in df.columns:
        series = df[col]
        print("col =====: ", col)
        print(series)

    print("================== 遍历行")
    for idx, row in df.iterrows():
        print("index =====: ", idx)
        print(row)


def getDataByColNameFun():
    """
    根据列名获取列数据
    :return:
    """
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    res = df[['col-1', 'col-2']]
    print(res)


def getDataByLocFun():
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 通过index获取单行数据
    get_one_by_index = df.loc["idx-1"]
    print(get_one_by_index)

    # 通过index获取多行数据
    get_muilt_by_index = df.loc[["idx-1", "idx-3"]]
    print(get_muilt_by_index)

    # 获取指定列(列名 col-1, col-3)
    get_mult_by_rows = df.loc[:, ['col-1', 'col-3']]
    print(get_mult_by_rows)

    # 获取指定列和指定行
    get_mult_by_col_rows = df.loc[["idx-1", "idx-3", "idx-5"], ['col-1', 'col-3']]
    print(get_mult_by_col_rows)


def getDataByIlocFun():
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 通过行数获取单行数据（0行）
    get_one_by_row = df.iloc[[0]]
    print(get_one_by_row)

    # 通过行数获取多行数据（0行， 3行）
    get_mult_by_row_1 = df.iloc[[0, 3]]
    print(get_mult_by_row_1)

    # 通过行数获取单行数据（0行 ~ 2行）
    get_mult_by_rows_2 = df.iloc[0:3]
    print(get_mult_by_rows_2)

    # 通过行数获取单行数据（1行 ~ 结尾）
    get_mult_by_rows_3 = df.iloc[1::]
    print(get_mult_by_rows_3)

    # 通过列数获取数据
    get_mult_by_col_1 = df.iloc[:, [0, 1]]
    print(get_mult_by_col_1)

    # 通过行数和列数获取数据
    get_mult_by_col_2 = df.iloc[[0, 3], [0, 1]]
    print(get_mult_by_col_2)

    # 通过行数和列数获取数据
    get_mult_by_col_3 = df.iloc[:, :3]
    print(get_mult_by_col_3)


def filterFun():
    """
    过滤数据
    :return:
    """
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)
    print(df)
    # 过滤单列
    filter_1 = df.loc[(df["col-1"] > 31)]
    print(filter_1)

    # 过滤多列
    filter_2 = df.loc[(df["col-1"] > 31) & (df["col-3"] >= 43)]
    print(filter_2)


def insertCol():
    """
    增加列
    :return:
    """
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 在末尾增加列
    df["col-6"] = [106, 206, 306, 406, 506]
    df.loc[:, 'col-7'] = [116, 216, 316, 416, 516]
    # 指定位置插入列
    df.insert(1, 'col-01', [216, 226, 236, 246, 256])

    print(df)


def insertRow():
    """
    插入行
    :return:
    """
    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 插入单行
    df.loc["idx-100"] = [101, 102, 103, 104]

    print(df)


def merge_rows():
    """
    合并行
    :return:
    """
    data_1 = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    data_2 = [
        [141, 142, 143, 144],
        [151, 152, 153, 154]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df_1 = pd.DataFrame(data=data_1, index=index, columns=columns)
    df_2 = pd.DataFrame(data=data_2, index=["idx-101", "idx-102"], columns=columns)

    # 插入单行
    df = df_1.append(df_2)
    print(df)


def merge_cols():
    """
    合并列
    :return:
    """
    data_1 = [
        ["a", 11],
        ["b", 12],
        ["c", 13],
        ["d", 14],
        ["e", 15]
    ]
    data_2 = [
        ["a", 'col-31', 'col-41'],
        ["b", 'col-32', 'col-42'],
        ["c", 'col-33', 'col-43'],
        ["d", 'col-34', 'col-44'],
        ["e", 'col-35', 'col-45']
    ]
    columns_1 = ['col-1', 'col-2']
    columns_2 = ['col-1', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df_1 = pd.DataFrame(data=data_1, index=index, columns=columns_1)
    df_2 = pd.DataFrame(data=data_2, index=index, columns=columns_2)

    # 合并
    merge_1 = pd.merge(df_1, df_2, on="col-1")
    merge_2 = pd.merge(df_1, df_2)
    print(merge_1)
    print(merge_2)


def renameColName():
    """
    修改列名
    :return:
    """
    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 修改列名
    df1 = df.copy()
    df1.columns = ['col-11', 'col-12', 'col-3', 'col-4']
    print(df1)

    # 修改列名
    df2 = df.copy().rename(columns={
        "col-1": "col-101",
        "col-2": "col-201",
        "col-3": "col-301"
    })
    print(df2)


def updateByLoc():
    """
    通过loc修改数据
    :return:
    """
    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 根据行标签 修改整行数据
    update_by_index = df.copy()
    update_by_index.loc["idx-1"] = [101, 102, 103, 104]
    print(update_by_index)

    # 根据列标签 修改整列数据
    update_by_column = df.copy()
    update_by_column.loc[:, "col-2"] = [111, 121, 131, 141, 151]
    print(update_by_column)

    # 修改某列某行
    update_element = df.copy()
    update_element.loc["idx-1", "col-2"] = 1002
    print(update_element)


def updateByIloc():
    """
    通过iloc修改数据
    :return:
    """
    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    update_element = df.copy()
    update_element.iloc[1, 1] = 202
    print(update_element)

    # 修改第一行数据
    update_row = df.copy()
    update_row.iloc[1, :] = [121, 122, 123, 124]
    print(update_row)

    # 修改第2列数据
    update_col = df.copy()
    update_col.iloc[:, 1] = [112, 122, 132, 142, 152]
    print(update_col)


def dropFun():
    """
    删除元素
    :return:
    """
    data = [
        [11, 12, 13, 14],
        [21, 22, 23, np.NaN],
        [31, 32, 33, np.NaN],
        [41, 42, 43, np.NaN],
        [51, 52, np.NaN, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)
    print(df)

    # 通过列名 删除col-1列
    drop_col = df.copy()
    drop_col.drop(["col-1"], axis=1, inplace=True)
    print(drop_col)

    # 通过columns 删除col-1列
    drop_col_by_columns = df.copy()
    drop_col_by_columns.drop(columns="col-1", inplace=True)
    print(drop_col_by_columns)

    # 通过标签 删除 col-1列
    drop_col_by_labels = df.copy()
    drop_col_by_labels.drop(labels="col-1", axis=1, inplace=True)
    print(drop_col_by_labels)

    # 通过index 删除 idx-1行
    drop_row_by_index = df.copy()
    drop_row_by_index.drop(index="idx-1", inplace=True)
    print(drop_row_by_index)

    # 通过标签 删除 idx-1行
    drop_row_by_labels = df.copy()
    drop_row_by_labels.drop(labels="idx-1", axis=0, inplace=True)
    print(drop_row_by_labels)

    # 通过index 删除idx-2, idx-3行
    drop_rows_by_index = df.copy()
    drop_rows_by_index.drop(["idx-2", "idx-3"], inplace=True)
    print(drop_rows_by_index)

    # 通过过滤条件删除单行
    drop_by_condition_1 = df.copy()
    index1 = drop_by_condition_1[drop_by_condition_1["col-1"].isin([41])].index[0]
    drop_by_condition_1.drop(index=index1, inplace=True)
    print(drop_by_condition_1)

    # 通过过滤条件删除多行
    drop_by_condition_2 = df.copy()
    index2 = drop_by_condition_2[drop_by_condition_2["col-1"].isin([41, 51])].index
    drop_by_condition_2.drop(index=index2, inplace=True)
    print(drop_by_condition_2)

    # 通过过滤条件删除多行
    drop_by_condition_3 = df.copy()
    index3 = drop_by_condition_3[drop_by_condition_3["col-1"] > 40].index
    drop_by_condition_3.drop(index=index3, inplace=True)
    print(drop_by_condition_3)

    # 通过组合过滤条件删除多行
    drop_by_condition_4 = df.copy()
    index4 = drop_by_condition_4[
        (drop_by_condition_4["col-1"].isin([41, 51])) & (drop_by_condition_4["col-2"] > 50)].index
    drop_by_condition_4.drop(index=index4, inplace=True)
    print(drop_by_condition_4)

    # 删除含有NAN的行
    drop_na_1 = df.copy()
    drop_na_1 = drop_na_1.dropna()
    print(drop_na_1)

    # 删除含有NAN的行
    drop_na_2 = df.copy()
    drop_na_2 = drop_na_2[drop_na_2["col-4"].notnull()]
    print(drop_na_2)


def showDataFrameInfo():
    """
    查看DataFram信息
    :return:
    """
    data = [
        [11, 12, "col-3-1", False],
        [21, 22, "col-3-2", True],
        [31, 32, np.NaN, None],
        [41, 42, np.NaN, False],
        [51, 52, "col-3-5", np.NaN]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # DataFrame 属性信息
    df_info = df.info()
    print(df_info)

    # 判断是否存在缺失值
    df_isnull = df.isnull()
    print(df_isnull)

    # 判断是否存在非空
    df_notnull = df.notnull()
    print(df_notnull)


def fillData():
    """
    填充空值
    :return:
    """
    data = [
        [11, 12, "col-3-1", False],
        [21, 22, "col-3-2", True],
        [31, 32, np.NaN, None],
        [41, 42, np.NaN, False],
        [51, 52, "col-3-5", np.NaN]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)
    print(df)
    # 填充空值
    df_fill = df.copy()
    df_fill['col-3'] = df["col-3"].fillna("col-3-default")
    print(df_fill)


def duplicated():
    """
    重复值处理
    :return:
    """
    data = [
        [11, 12, "col-3-1", False],
        [11, 12, "col-3-1", False],
        [21, 22, "col-3-2", True],
        [31, 22, np.NaN, None],
        [31, 22, np.NaN, None],
        [41, 22, np.NaN, False],
        [51, 52, "col-3-5", np.NaN]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5', 'idx-6', 'idx-7']

    df = pd.DataFrame(data=data, index=index, columns=columns)
    print(df)
    # 查看重复行
    duplicated_info = df.duplicated()
    print(duplicated_info)

    # 删除重复行
    duplicated_drop_1 = df.copy()
    duplicated_drop_1 = duplicated_drop_1.drop_duplicates()
    print(duplicated_drop_1)

    # 删除特定列的重复行，并保留第一个重复行
    duplicated_drop_2 = df.copy()
    duplicated_drop_2 = duplicated_drop_2.drop_duplicates(["col-2"])
    print(duplicated_drop_2)

    # 删除特定列的重复行，并保留最后一个重复行
    duplicated_drop_3 = df.copy()
    duplicated_drop_3 = duplicated_drop_3.drop_duplicates(["col-2"], keep="last")
    print(duplicated_drop_3)

    # inplace = True 表示直接在原DataFrame对象上删除重复项， 而默认值False表示删除重复项后再生成一个副本。
    duplicated_drop_4 = df.copy()
    duplicated_drop_4 = duplicated_drop_4.drop_duplicates(["col-2", "col-3"], inplace=False)
    print(duplicated_drop_4)


def reIndex():
    """
    重新设定索引和columns
    :return:
    """
    data = [
        [11, 12, 13, 14],
        [21, 22, 23, np.nan],
        [31, 32, 33, 34],
        [41, 42, 43, np.nan],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 重置行索引
    reindex = df.copy()
    reindex = reindex.reindex(['idx-0', 'idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5', 'idx-10'])
    print(reindex)

    # 重置列索引
    recolumn = df.copy()
    recolumn = recolumn.reindex(columns=['col-0', 'col-1', 'col-2', 'col-3', 'col-4', 'col-10'])
    print(recolumn)

    # 重置行、列索引
    reindexAndColumn = df.copy()
    reindexAndColumn = reindexAndColumn.reindex(
        index=['idx-0', 'idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5', 'idx-10'],
        columns=['col-0', 'col-1', 'col-2', 'col-3', 'col-4', 'col-10'])
    print(reindexAndColumn)

    # 删除并重新设置索引
    reSetIndex = df.copy()
    reSetIndex = reSetIndex.dropna().reset_index(drop=True)
    print(reSetIndex)


def setIndex():
    """
    将列设置索引
    :return:
    """

    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        ["组织结构-1", "组织结构-1-1", 11, 12],
        ["组织结构-1", "组织结构-1-2", 11, 12],
        ["组织结构-1", "组织结构-1-3", 11, 12],
        ["组织结构-1", "组织结构-1-3", 13, 14],
        ["组织结构-2", "组织结构-2-1", 21, 22],
        ["组织结构-2", "组织结构-2-1", 21, 22],
        ["组织结构-2", "组织结构-2-3", 21, 22],
        ["组织结构-3", "组织结构-3-1", 31, 32],
        ["组织结构-3", "组织结构-3-1", 31, 32],
        ["组织结构-3", "组织结构-3-3", 31, 32],
        ["组织结构-4", "组织结构-4-1", 41, 42],
        ["组织结构-4", "组织结构-4-1", 41, 42],
        ["组织结构-4", "组织结构-4-3", 41, 42]
    ]
    columns = ['一级单位', '二级单位', '男员工人数', '女员工人数']

    df = pd.DataFrame(data=data, columns=columns)

    # 单列将列设置为索引
    setIndex_one = df.copy()
    setIndex_one = setIndex_one.set_index(["一级单位"])
    print(setIndex_one)

    # 多列设置为索引
    setIndex_two = df.copy()
    setIndex_two = setIndex_two.set_index(["一级单位", "二级单位"])
    print(setIndex_two)


def sortFun():
    """
    排序
    :return:
    """
    data = [
        [51, 52, 53, 'c'],
        [31, 32, 33, 'b'],
        [21, 22, 23, 'b'],
        [41, 42, 43, 'c'],
        [11, 12, 13, 'a']
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)
    print(df)
    # 降序
    desc = df.sort_values(by="col-1", ascending=False)
    print(desc)

    # 升序
    asc = df.sort_values(by="col-1", ascending=True)
    print(asc)

    # 分组之后并排序
    group_desc = df.groupby(["col-4"])["col-1"].sum().reset_index()
    print(group_desc)

    # 对某列进行降序，并显示排名
    rank_df = df.sort_values(by='col-1', ascending=False)
    rank_df['sort-num'] = rank_df['col-1'].rank(method="first", ascending=False)
    print(rank_df[['col-4', 'col-1', 'sort-num']])

    # 对某列进行降序，并显示排名
    rank_df_1 = df.copy()
    rank_df_1['sort-num'] = rank_df_1['col-1'].rank(ascending=False)
    print(rank_df_1)


def genericCalFun():
    """
    一些常用计算行数
    :return:
    """
    data = [
        [1, 1, 1, 1],
        [2, 1, 2, 1],
        [3, 1, 3, 1],
        [4, 1, 4, 1],
        [5, 1, 5, 1]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    print(df)

    # 行求和
    row_sum = df.sum(axis=1)

    # 列求和
    row_col = df.sum(axis=0)

    # 平均值
    mean = df.mean()

    # 最大值
    max = df.max()

    # 最小
    min = df.min()

    # 中位数
    median = df.median()

    # 众数
    mode = df.mode()

    # 方差
    var = df.var(axis=1)

    # 标准差
    std = df.std(axis=1)

    # 分位数也称分位点，它以概率为依据将数据分割为几个等份（中位数（即二分位数）、四分位数）
    quantile = df.quantile(0.35)

    print(row_sum)
    print(row_col)
    print(mean)
    print(max)
    print(min)
    print(median)
    print(mode)
    print(var)
    print(std)
    print(quantile)


def dataFormatRound():
    """
    数据格式化 - 四舍五入
    :return:
    """

    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [100011.12345, 200012.34567, 300013.56789],
        [100021.12345, 200022.34567, 300023.56789],
        [100031.12345, 200032.34567, 300033.56789],
        [100041.12345, 200042.34567, 300043.56789],
        [100051.12345, 200052.34567, 300053.56789]
    ]
    columns = ['col-1', 'col-2', 'col-3']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)
    print(df)

    # 整体数据四舍五入取2位
    df_1 = df.copy()
    df_1 = df_1.round(2)
    print(df_1)

    # 'col-1' 列数据四舍五入取1位, 'col-2'列数据四舍五入取3位
    df_2 = df.copy()
    df_2 = df_2.round({"col-1": 1, "col-2": 3})
    print(df_2)


def dataFormatApply():
    """
    数据格式化 - apply
    :return:
    """

    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [100011.12345, 200012.34567, 300013.56789, 400014.789],
        [100021.12345, 200022.34567, 300023.56789, 400024.789],
        [100031.12345, 200032.34567, 300033.56789, 400034.789],
        [100041.12345, 200042.34567, 300043.56789, 400044.789],
        [100051.12345, 200052.34567, 300053.56789, 400054.789]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)
    print(df)

    res = pd.DataFrame()
    res["百分比- 整数"] = df["col-1"].apply(lambda item: format(item, ".0%"))
    res["百分比- 三位小数"] = df["col-1"].apply(lambda item: format(item, ".3%"))
    res["千位分隔符"] = df["col-1"].apply(lambda item: format(item, ","))
    res["转换int并千位分隔符"] = df["col-1"].apply(lambda item: format(int(item), ","))
    print(res)


def dataFormatMap():
    """
    数据格式化 - map
    :return:
    """

    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [100011.12345, 200012.34567, 300013.56789, 400014.789],
        [100021.12345, 200022.34567, 300023.56789, 400024.789],
        [100031.12345, 200032.34567, 300033.56789, 400034.789],
        [100041.12345, 200042.34567, 300043.56789, 400044.789],
        [100051.12345, 200052.34567, 300053.56789, 400054.789]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    df["百分比- 二位小数"] = df["col-1"].map(lambda item: '{:.2%}'.format(item))

    print(df)


def dataFormatGroup():
    """
    分组统计
    :return:
    """
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        ["组织结构-1", "组织结构-1-1", 11, 12],
        ["组织结构-1", "组织结构-1-2", 11, 12],
        ["组织结构-1", "组织结构-1-3", 11, 12],
        ["组织结构-1", "组织结构-1-3", 13, 14],
        ["组织结构-2", "组织结构-2-1", 21, 22],
        ["组织结构-2", "组织结构-2-1", 21, 22],
        ["组织结构-2", "组织结构-2-3", 21, 22],
        ["组织结构-3", "组织结构-3-1", 31, 32],
        ["组织结构-3", "组织结构-3-1", 31, 32],
        ["组织结构-3", "组织结构-3-3", 31, 32],
        ["组织结构-4", "组织结构-4-1", 41, 42],
        ["组织结构-4", "组织结构-4-1", 41, 42],
        ["组织结构-4", "组织结构-4-3", 41, 42]
    ]
    columns = ['一级单位', '二级单位', '男员工人数', '女员工人数']

    df = pd.DataFrame(data=data, columns=columns)
    print(df)
    # 按单列进行分组统计
    group_one_org = df.groupby('一级单位').sum()
    print(group_one_org)

    # 按多列进行分组统计
    group_two_org = df.groupby(['一级单位', '二级单位']).sum()
    print(group_two_org)

    # 按单列进行分组统计，并统计男员工人数
    group_one_org_by_man = df.groupby(['一级单位'])["男员工人数"].sum()
    print(group_one_org_by_man)

    # 迭代
    groups = df.groupby(['一级单位'])
    for name, group in groups:
        print(name)
        print(group)

    # 聚合函数 (平均、求和、最大、最小）
    agg_1 = df.groupby("一级单位").agg(['mean', 'sum', 'max', 'min'])
    print(agg_1)

    # 聚合函数 分列聚合计算
    agg_2 = df.groupby("一级单位").agg({'男员工人数': ['mean', 'sum'], '女员工人数': ['max', 'min']})
    print(agg_2)

    # 通过自定义函数实现分组统计
    agg_3 = df.agg({
        '一级单位': [lambda item: item.value_counts(dropna=False).index[0]],
        '男员工人数': ['mean', 'sum'],
        '女员工人数': ['max', 'min']
    })
    print(agg_3)


def diffFun():
    """
    差分
    :return:
    """
    data = [
        [10, 12],
        [12, 22],
        [17, 32],
        [15, 42],
        [20, 52]
    ]
    columns = ['col-1', 'col-2']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    df['差分'] = df['col-1'] - df['col-1'].shift()
    print(df)


def splitFun():
    """
    差分
    :return:
    """
    data = [
        ["a,b"],
        ["a,b"],
        ["a,b"],
        ["a,b"],
        ["a,b"]
    ]
    columns = ['col-1']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    split_info = df['col-1'].str.split(",", expand=True)
    df["part-1"] = split_info[0]
    df["part-2"] = split_info[1]
    print(df)


def stackFun():
    """
    二维表与一维表之间转换
    :return:
    """
    # 解决数据输出时列名不对齐的问题
    pd.set_option('display.unicode.ambiguous_as_wide', True)
    pd.set_option('display.unicode.east_asian_width', True)

    data = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44],
        [51, 52, 53, 54]
    ]
    columns = ['col-1', 'col-2', 'col-3', 'col-4']
    index = ['idx-1', 'idx-2', 'idx-3', 'idx-4', 'idx-5']

    df = pd.DataFrame(data=data, index=index, columns=columns)

    # 二维表转化成一维表 DataFrame -> Series
    stack = df.stack()
    print("-----dd")
    print(stack)
    print("-----dd")

    # 一维表转化成二维表 Series -> DataFrame
    unstack = stack.unstack()
    print(unstack)


if __name__ == '__main__':
    diffFun()
    # genericCalFun()
    # dataFormatGroup()

    # stackFun()
    # duplicated()
    # fillData()

    # splitFun()
    # dataFormatMap()
    # dataFormatApply()
    # dataFormatRound()

    # showDataFrameInfo()

    # setIndex()
    # reIndex()
    # renameColName()

    # updateByIloc()
    # updateByLoc()
    # merge_rows()
    # merge_cols()
    # filterFun()
    # sortFun()
    # dropFun()
    # insertRow()
    # insertCol()
    # getDataByIlocFun()
    # getDataByLocFun()
    # getDataByColNameFun()
    # forFun()
    # createDataframe()
